﻿using System.Data.OracleClient;
using System.Data;

namespace ConnectDB
{
    public class Connect_DataBase
    {
        #region khai bao bien
        string _connectstring = "Data Source=FLEXTEST_109;Persist Security Info=True;User ID=HOSTTEST;Password=HOST;";
        OracleDataAdapter _da = new OracleDataAdapter();
        OracleCommand _cmd = new OracleCommand();
        OracleConnection _con = new OracleConnection();
        DataTable _table = new DataTable();
        #endregion

        #region ham
        private void OpenConnect()
        {
            _con.ConnectionString = _connectstring;
            _con.Open();
        }

        private void CloseConnect()
        {
            _con.Close();
        }

        // check login
        public bool CheckLogin(string _username, string _password)
        {
            _cmd = _con.CreateCommand();
            _cmd.CommandType = CommandType.Text;
            _cmd.CommandText = "SELECT * FROM tbl_user WHERE username like :username and pass like :password AND Active='Y'";
            _cmd.Parameters.Add("username", OracleType.VarChar).Value = _username;
            _cmd.Parameters.Add("password", OracleType.VarChar).Value = _password;
            _cmd.Connection = _con;
            OpenConnect();
            _da = new OracleDataAdapter(_cmd);
            _da.Fill(_table);
            CloseConnect();
            if (_table.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        public DataTable Return_Datatable(string _sql)
        {
            OpenConnect();
            _da = new OracleDataAdapter(_sql, _con);
            DataTable result = new DataTable();
            _da.Fill(result);
            CloseConnect();
            return result;
        }

        public DataTable SearchView_BySQL(string _sql)
        {
            OpenConnect();
            _da = new OracleDataAdapter(_sql, _con);
            DataTable result = new DataTable();
            _da.Fill(result);
            CloseConnect();
            return result;
        }

        #endregion
    }
}
